﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Secured_IncomeList : System.Web.UI.Page
{
    SqlConnection conn;
    SqlCommand cmd;
    SqlDataAdapter da;
    DataSet ds;
    SqlDataReader dr;
    protected void Page_Load(object sender, EventArgs e)
    {
        conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
        if (conn.State != ConnectionState.Open)
        {
            try
            {
                conn.Open();
            }
            catch (SqlException ex)
            {
                ltMsg.Text = ex.Message;
            }
        }
        if (!Page.IsPostBack)
        {
            FillGrid();
            FillYear(DateTime.Today.Year);
            FillExpenseHeads();
            FillUsers();
        }
    }
    private void FillUsers()
    {
        cmd = new SqlCommand("Select UserName from HAT_UserAccounts where IsApproved=@IsApproved order by UserName asc", conn);
        cmd.Parameters.Add("@IsApproved", System.Data.SqlDbType.Bit).Value = true;
        try
        {
            dr = cmd.ExecuteReader();
            cmbUsers.Items.Add(string.Empty);
            while (dr.Read())
            {
                cmbUsers.Items.Add(dr.GetString(0).ToString());
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FindSum()
    {
        cmd = new SqlCommand("Select sum(IncomeAmount) from HAT_Income", conn);
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtTotal.Text = dr.GetValue(0).ToString();
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FillExpenseHeads()
    {
        cmbExpenseHead.Items.Clear();
        cmd = new SqlCommand("Select ExpenseHead from HAT_ExpenseHead order by ExpenseHead asc", conn);
        try
        {
            dr = cmd.ExecuteReader();
            cmbExpenseHead.Items.Add(string.Empty);
            while (dr.Read())
            {
                cmbExpenseHead.Items.Add(dr.GetString(0));
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FillYear(int CurrentYear)
    {
        cmbYear.Items.Clear();
        while (CurrentYear <= 2020)
        {
            cmbYear.Items.Add(CurrentYear.ToString());
            CurrentYear += 1;
        }
    }
    private void FillGrid()
    {
        cmd = new SqlCommand("Select IncomeID, IncomeDate, ExpenseHead, PaidBy, ForPeriod, IncNotes, Comments, IncomeAmount from HAT_Income", conn);
        da = new SqlDataAdapter(cmd.CommandText, conn);
        ds = new DataSet();
        try
        {
            da.Fill(ds, "NewTable");
            grdIncome.DataSource = ds.Tables["NewTable"].DefaultView;
            grdIncome.DataBind();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
        FindSum();
    }
    protected void chkFilterByMonthYear_CheckedChanged(object sender, EventArgs e)
    {
        if (chkFilterByMonthYear.Checked == true)
        {
            cmbExpenseHead.Enabled = true;
            cmbYear.Enabled = true;
        }
    }
}